package com.eastcom.providor.dao;

import java.io.Serializable;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.Collection;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.criterion.DetachedCriteria;
import org.springframework.orm.hibernate3.support.HibernateDaoSupport;
import org.springframework.util.NumberUtils;

import com.eastcom.bm.atom.Atom_DatasetDefine;

public class BaseDao  extends HibernateDaoSupport implements IDao{

	protected DecimalFormat format= new DecimalFormat("#0.00");
	
	protected DecimalFormat ratioFormat= new DecimalFormat("##0.00");
	
	protected String datePattern="yyyy-MM-dd HH:mm:ss";
	
	protected Class objectClass;

	public void createObject(Object obj) {
		getHibernateTemplate().save(obj);
	}

	public void updateObject(Object obj) {
		getHibernateTemplate().update(obj);
	}

	public void updateObjects(String hql, Object[] values) throws Exception {
		this.executeUpdate(hql, values);
	}

	public void saveObject(Object obj) {
		getHibernateTemplate().saveOrUpdate(obj);
	}

	public void saveObjects(Collection entities) {
		getHibernateTemplate().saveOrUpdateAll(entities);
	}
    public void deleteObject(Class clazz,Serializable id){
    	Object obj = getHibernateTemplate().get(clazz,id);
    	if(obj!=null) getHibernateTemplate().delete(obj);
    	this.flush();
    }
	public void deleteObjectByID(Serializable id) {
		Object obj = getObjectByID(id);
		if(obj!=null)getHibernateTemplate().delete(obj);
	}

	public void deleteObject(Object obj) {
		getHibernateTemplate().delete(obj);
	}

	public void deleteObjects(String hql, Object[] values) throws Exception {
		this.executeUpdate(hql, values);
	}

	public void deleteObjects(Collection entities) {
		this.getHibernateTemplate().deleteAll(entities);
	}
	
	@SuppressWarnings("unchecked")
	public List getObjects() {
		return getObjects(this.getObjectClass());
	}

	@SuppressWarnings("unchecked")
	public <T>List<T>  getObjects(Class<T> clazz) {
		return getHibernateTemplate().loadAll(clazz);
	}

	public List getObjects(int firstResult, int maxResults) {
		return getObjects("from " + getObjectClassName(), firstResult, maxResults);
	}

	public List getObjects(String hql) {
		return getHibernateTemplate().find(hql);
	}

	public List getObjects(String hql, Object[] args) {
		return getHibernateTemplate().find(hql, args);
	}

	public List getObjects(String hql, int firstResult, int maxResults) {
		return getObjects(hql, (Object[]) null, firstResult, maxResults);
	}

	public List getObjects(String hql, Object[] args, int firstResult, int maxResults) {
		return this.executeSelect(hql, args, firstResult, maxResults);
	}

	public List getObjectsByCriteria(DetachedCriteria criteria) {
		return getHibernateTemplate().findByCriteria(criteria);
	}

	public List getObjectsByCriteria(DetachedCriteria criteria, int firstResult, int maxResults) {
		return getHibernateTemplate().findByCriteria(criteria, firstResult, maxResults);
	}

	public List getObjectsByNamedQuery(String queryName) {
		return getHibernateTemplate().findByNamedQuery(queryName);
	}

	public List getObjectsByNamedQuery(String queryName, Object[] values) {
		return getHibernateTemplate().findByNamedQuery(queryName, values);
	}

	public List getObjectsByNamedParam(String queryString, String[] paramNames, Object[] values) {
		return getHibernateTemplate().findByNamedParam(queryString, paramNames, values);
	}

	public List getObjectsByNamedQueryAndNamedParam(String queryName, String[] paramNames, Object[] values) {
		return getHibernateTemplate().findByNamedQueryAndNamedParam(queryName, paramNames, values);
	}

	public int getObjectCount() {
		return getObjectCount("SELECT COUNT(*) FROM " + this.getObjectClassName());

	}

	public int getObjectCount(String hql) {
		return getObjectCount(hql, (Object[]) null);

	}

	public int getObjectCount(String hql, Object[] args) {
		return executeCount(hql, args);
	}

	public Object getObject(Serializable id) {
		return getObjectByID(id);
	}

	@SuppressWarnings("unchecked")
	public Object getObjectByID(Serializable id) {
		return getObject(this.getObjectClass(), id);
	}

	@SuppressWarnings("unchecked")
	public <T>T getObject(Class<T> clazz, Serializable id) {
		return  (T) getHibernateTemplate().get(clazz, id);
	}

	public Object loadObject(Class clazz, Serializable id) {
		return getHibernateTemplate().load(clazz, id);
	}

	public Object mergeObject(Object entity) {
		return getHibernateTemplate().merge(entity);
	}

	public void persistObject(Object entity) {
		getHibernateTemplate().persist(entity);
	}

	public void refreshObject(Object entity) {
		getHibernateTemplate().refresh(entity);
	}

	public void evictObject(Object entity) {
		getHibernateTemplate().evict(entity);
	}

	public void flush() {
		getHibernateTemplate().flush();
	}

	public void clear() {
		getHibernateTemplate().clear();
	}

	public void executeUpdate(String hql, Object[] values) throws Exception{
		Session session=this.getSession();
		try{
		    Query query = session.createQuery(hql);
		    if (values != null) {
			    for (int i = 0; i < values.length; i++) {
				    query.setParameter(i, values[i]);
			    }
		    }
		    query.executeUpdate();
		}
		catch(Exception e){
			throw e;
		}
		finally{
			if(session!=null)
				this.releaseSession(session);
		}
	}

	public void executeSqlUpdate(String sql, Object[] values)throws Exception {
		Session session=this.getSession();
		try{
		    SQLQuery query = session.createSQLQuery(sql);
		    if (values != null) {
			    for (int i = 0; i < values.length; i++) {
				   query.setParameter(i, values[i]);
			    }
		    }
		    query.executeUpdate();
		}
		catch(Exception e){
			throw e;
		}
		finally{
			if(session!=null)
				this.releaseSession(session);
		}
	}
	
	public List executeSelect(String hql, Object[] values, int firstResult, int maxResults) {
		Query query = this.getSession().createQuery(hql);
		if (values != null) {
			for (int i = 0; i < values.length; i++) {
				query.setParameter(i, values[i]);
			}
		}
		query.setFirstResult(firstResult);
		query.setMaxResults(maxResults);
		return query.list();
	}

	public Object executeSelectForObject(String hql, Object[] values) {
		Query query = this.getSession().createQuery(hql);
		if (values != null) {
			for (int i = 0; i < values.length; i++) {
				query.setParameter(i, values[i]);
			}
		}
		if (query != null) {
			return query.list().isEmpty() ? null : query.list().get(0);
		} else {
			return null;
		}
	}

	public int executeCount(String hql, Object[] values) {
		if(!hql.toUpperCase().contains("SELECT COUNT(*) FROM".trim()))
			hql="SELECT COUNT(*)"+hql;
		Query query = this.getSession().createQuery(hql);
		if (values != null) {
			for (int i = 0; i < values.length; i++) {
				query.setParameter(i, values[i]);
			}
			
		}
		if(query.uniqueResult()==null)
			return 0;
		return NumberUtils.parseNumber(query.uniqueResult().toString(), Integer.class).intValue();
	}

	protected String getObjectClassName() {
		return getObjectClass().getName();
	}

	public Class getObjectClass() {
		return objectClass;
	}

	public void setObjectClass(Class objectClass) {
		this.objectClass = objectClass;
	}
	
	public <T>List<T> getSQLObjects(String sql,Class<T> clazz) throws Exception{
		Session session=null;
		
		try{
			session=this.getSession();
		    SQLQuery query=session.createSQLQuery(sql).addEntity(clazz);
		    return query.list();
		}
		catch(Exception e){
			throw e;
		}
		finally{
			if(session!=null)
			    this.releaseSession(session);
		}
	}
	
	public <T>List<T> getSQLObjects(String sql,Class<T> clazz,Object[] values){
		SQLQuery query=this.getSession().createSQLQuery(sql).addEntity(clazz);
		if (values != null) {
			for (int i = 0; i < values.length; i++) {
				query.setParameter(i, values[i]);
			}
		}
		return query.list();
	}
	
	public Map<Object,List<Map>> getFieldMapLstWithKeyFildBySQL(String sql,String keyField,String[] valueFields ) throws Exception{
		if(valueFields==null||valueFields.length==0)
			return null;

		Connection connection=null;
		PreparedStatement psta=null;
		ResultSet rs=null;
		Session session=null;
		try{
			session=this.getSession();
		    connection=session.connection();
		    
		    psta=connection.prepareStatement(sql);
		    rs=psta.executeQuery();
		    Map<Object,List<Map>> result=new HashMap<Object,List<Map>> ();

		    while(rs.next()){
		    	Object key=rs.getObject(keyField) instanceof java.util.Date?rs.getTimestamp(keyField):rs.getObject(keyField);
		    	List<Map> valueMapLst=result.get(key);
		    	if(valueMapLst==null){
		    		valueMapLst=new ArrayList<Map>();
		    		result.put(key, valueMapLst);
		    	}
		    	
		    	Map valueMap=new HashMap();
		    	valueMapLst.add(valueMap);

		    	for(String valueField:valueFields)
		    		if(rs.getObject(valueField) instanceof java.util.Date){
		    			valueMap.put(valueField, rs.getTimestamp(valueField));
		    		}
		    		else
		    		    valueMap.put(valueField, rs.getObject(valueField));
		    }

		    return result;
		}
		catch(SQLException e){
			throw e;
		}
		finally{
			    doClose(rs,psta,null);
                this.releaseSession(session);
		}
	}
	
	public Map<Object,Map> getFieldMapWithKeyFildBySQL(String sql,String keyField,String[] valueFields) throws Exception{
		if(valueFields==null||valueFields.length==0)
			return null;
		
		Session session=null;
		Connection connection=null;
		PreparedStatement psta=null;
		ResultSet rs=null;
		Boolean autoCommit=null;
		
		try{
			session=this.getSession();
		    connection=session.connection();
		    if(connection==null||connection.isClosed())
		    	throw new Exception("baseDao getFieldMapWithKeyFildBySQL connection closed exception");
		    
		    psta=connection.prepareStatement(sql);
		    rs=psta.executeQuery();
		    Map<Object,Map> result=new HashMap<Object,Map> ();

		    while(rs.next()){
		    	Object key=rs.getObject(keyField) instanceof java.util.Date?rs.getTimestamp(keyField):rs.getObject(keyField);
		    	Map valueMap=result.get(key);
		    	if(valueMap==null){
		    		valueMap=new HashMap();
		    		result.put(key, valueMap);
		    	}
		    	
		    	for(String valueField:valueFields)
		    		if(rs.getObject(valueField) instanceof java.util.Date){
		    			valueMap.put(valueField, rs.getTimestamp(valueField));
		    		}
		    		else
		    		    valueMap.put(valueField, rs.getObject(valueField));
		    }

		    return result;
		}
		catch(SQLException e){
			throw e;
		}
		finally{
			doClose(rs,psta,null);
            this.releaseSession(session);
		}
		
	}
	
	public Map<Object,Map> getFieldMapLstWithKeyFildsBySQL(String sql,String[] keyFields,String[] valueFields) throws Exception{
		if(keyFields==null||keyFields.length==0||valueFields==null||valueFields.length==0)
			return null;
		
		Session session=null;
		Connection connection=null;
		PreparedStatement psta=null;
		ResultSet rs=null;
		Boolean autoCommit=null;
		
		try{
			session=getSession();
		    connection=session.connection();
		    if(connection==null||connection.isClosed())
		    	throw new Exception("baseDao getFieldMapLstWithKeyFildsBySQL connection closed exception");
		    
		    psta=connection.prepareStatement(sql);
		    rs=psta.executeQuery();
		    Map<Object,Map> result=new HashMap<Object,Map> ();

		    while(rs.next()){
		    	String key="";
		    	for(String keyField:keyFields)
		    		key+=rs.getObject(keyField)==null?" @#":
		    			rs.getObject(keyField) instanceof java.util.Date?rs.getTimestamp(keyField).toString()+"@#":rs.getObject(keyField).toString()+"@#";
		    	
		    	Map valueMap=result.get(key.trim());
		    	if(valueMap==null){
		    		valueMap=new HashMap();
		    		result.put(key.trim(), valueMap);
		    	}
		    	
		    	for(String valueField:valueFields)
		    		if(rs.getObject(valueField) instanceof java.util.Date){
		    			valueMap.put(valueField, rs.getTimestamp(valueField));
		    		}
		    		else
		    		    valueMap.put(valueField, rs.getObject(valueField));
		    }

		    return result;
		}
		catch(SQLException e){
			throw e;
		}
		finally{
			doClose(rs,psta,null);
            this.releaseSession(session);
		}
	}
	
	public List<Map> getFieldsBySQLFromSource(String sql,String []fields,Atom_DatasetDefine datasetDefine) throws Exception{
		if(fields==null||fields.length==0)
			return null;
		
		Connection connection=null;
		PreparedStatement psta=null;
		ResultSet rs=null;
		
		try{
			String url=null;
			if("oracle".equalsIgnoreCase(datasetDefine.getDbType())){
			    Class.forName("oracle.jdbc.driver.OracleDriver");
			    url="jdbc:oracle:thin:@"+datasetDefine.getHost()+":"+datasetDefine.getPort()+":"+datasetDefine.getSid();
			}
			else{
				Class.forName("com.sybase.jdbc2.jdbc.SybDriver");
				url="jdbc:sybase:Tds:"+datasetDefine.getHost()+":"+datasetDefine.getPort()+"/"+datasetDefine.getSid()+"?charset=eucgb";
			}
			
			connection=DriverManager.getConnection(url,datasetDefine.getUserName(),datasetDefine.getPassWord());
		    psta=connection.prepareStatement(sql);
		    rs=psta.executeQuery();
		    List results=new ArrayList();
		    
		    while(rs.next()){
			    Map result=new HashMap();
			    Object value=null;
			    for(int i=0;i<fields.length;i++){
			       value=rs.getObject(fields[i]) instanceof java.util.Date?rs.getTimestamp(fields[i]):rs.getObject(fields[i]);
			       result.put(fields[i], value);
			    }
			    results.add(result);
		    }

		    return results;
		}
		catch(Exception e){
			throw e;
		}
		finally{
			doClose(rs,psta,connection);
		}
	}
	
	public Map<Object,Map> getFieldMapWithKeyFildBySQLFromSource(String sql,String keyField,String[] valueFields,Atom_DatasetDefine datasetDefine) throws Exception{
		if(keyField==null||valueFields==null||valueFields.length==0)
			return null;
		
		Connection connection=null;
		PreparedStatement psta=null;
		ResultSet rs=null;
		
		try{
			String url=null;
			if("oracle".equalsIgnoreCase(datasetDefine.getDbType())){
			    Class.forName("oracle.jdbc.driver.OracleDriver");
			    url="jdbc:oracle:thin:@"+datasetDefine.getHost()+":"+datasetDefine.getPort()+":"+datasetDefine.getSid();
			}
			else{
				Class.forName("com.sybase.jdbc2.jdbc.SybDriver");
				url="jdbc:sybase:Tds:"+datasetDefine.getHost()+":"+datasetDefine.getPort()+"/"+datasetDefine.getSid()+"?charset=eucgb";
			}
			
			connection=DriverManager.getConnection(url,datasetDefine.getUserName(),datasetDefine.getPassWord());
		    psta=connection.prepareStatement(sql);
		    rs=psta.executeQuery();
		    Map<Object,Map> result=new HashMap<Object,Map> ();

		    while(rs.next()){
//		    	Object key=rs.getObject(keyField) instanceof java.util.Date?rs.getTimestamp(keyField):rs.getObject(keyField);
		    	Object key=null;
		    	if(rs.getObject(keyField) instanceof java.util.Date||rs.getObject(keyField) instanceof oracle.sql.TIMESTAMP)
		    	{
		    		key=rs.getTimestamp(keyField);
		    	}else{
		    		key=rs.getObject(keyField);
		    	}
		    	Map valueMap=result.get(key);
		    	if(valueMap==null){
		    		valueMap=new HashMap();
		    		result.put(key, valueMap);
		    	}
		    	
		    	for(String valueField:valueFields)
		    		if(rs.getObject(valueField) instanceof java.util.Date){
		    			valueMap.put(valueField, rs.getTimestamp(valueField));
		    		}
		    		else
		    		    valueMap.put(valueField, rs.getObject(valueField));
		    }

		    return result;
		}
		catch(SQLException e){
			throw e;
		}
		finally{
			doClose(rs,psta,connection);
		}
		
	}
	
	public List<Map> getFieldsBySQL(String sql,String []fields) throws Exception{
		if(fields==null||fields.length==0)
			return null;
		
		Session session=null;
		Connection connection=null;
		PreparedStatement psta=null;
		ResultSet rs=null;
		
		try{
			session=this.getSession();

		    connection=session.connection();
		    if(connection==null||connection.isClosed())
		    	throw new Exception("");
		    
		    psta=connection.prepareStatement(sql);
		    rs=psta.executeQuery();
		    List results=new ArrayList();
		    
		    while(rs.next()){
			    Map result=new HashMap();
			    Object value=null;
			    for(int i=0;i<fields.length;i++){
			       value=rs.getObject(fields[i]) instanceof java.util.Date?rs.getTimestamp(fields[i]):rs.getObject(fields[i]);
			       result.put(fields[i], value);
			    }
			    results.add(result);
		    }

		    return results;
		}
		catch(SQLException e){
			throw e;
		}
		finally{
			doClose(rs,psta,null);
            this.releaseSession(session);
		}
	}
	
	protected boolean appeandNotNullParam(StringBuffer sql,String columnName,Object value,Object appendValue,String operationType,boolean firstCondition){
		if(value==null||"".equals(value.toString().trim()))
			return firstCondition;
		
		if(firstCondition){
			sql.append(" where "+columnName+" "+operationType+"'"+appendValue+"'");
			firstCondition=false;
		}
		else
			sql.append(" and "+columnName+" "+operationType+"'"+appendValue+"'");
		
		return firstCondition;
	}
	
	protected boolean appeandExpressWhenValueNotNull(StringBuffer sql,String columnName,Object value,Object appendExpress,String operationType,boolean firstCondition){
		if(value==null||"".equals(value.toString().trim()))
			return firstCondition;
		
		if(firstCondition){
			sql.append(" where "+columnName+operationType+appendExpress);
			firstCondition=false;
		}
		else
			sql.append(" and "+columnName+operationType+appendExpress);
		
		return firstCondition;
	}
	
	protected boolean mergeSQLbyFieldLst(StringBuffer sb,String fieldName,Collection values,boolean firstCondition){
		if(values==null||values.isEmpty())
			return firstCondition;
		
		if(!firstCondition)
			sb.append(" and "+fieldName+" in(");
		else
			sb.append(" where "+ fieldName+" in(");
		
		Iterator it=values.iterator();
		boolean firstValue=true;
		while(it.hasNext()){
			String field= it.next().toString();
			if(firstValue){
				sb.append("'"+field.trim()+"'");
				firstValue=false;
			}
			else
				sb.append(",'"+field.trim()+"'");
			   
		}
		sb.append(")");
		
		return false;
	}
	
	protected boolean mergeSQLbyUnionFieldLst(StringBuffer sb,String fieldName,Collection values,boolean firstCondition){
		if(values==null||values.isEmpty())
			return firstCondition;
		
		if(!firstCondition)
			sb.append(" and "+fieldName+" in(");
		else
			sb.append(" where "+ fieldName+" in(");
		
		Iterator it=values.iterator();
		boolean firstValue=true;
		while(it.hasNext()){
			String field= it.next().toString();
			if(firstValue){
				sb.append(field.trim());
				firstValue=false;
			}
			else
				sb.append(","+field.trim());
			   
		}
		sb.append(")");
		
		return false;
	}
	
	protected boolean mergeSelectParam(StringBuffer sb,List<String> params,boolean firstParam){
		if(params==null||params.isEmpty())
			return firstParam;
		
		for(String param:params){
			if(firstParam){
			    sb.append(" "+param);
			    firstParam=false;
			}
			else
				sb.append(","+param);
		}
		
		return firstParam;
	}
	
	protected boolean appendNotNullDateAfterCondition(StringBuffer sql,String fieldName,Object value,boolean firstCondition){
		if(sql==null)
			return true;
		
		if(value!=null&&!"".equals(value.toString().trim())){
			if(firstCondition){
			    sql.append(" where "+fieldName+">=to_date('"+value+"','yyyy-mm-dd hh24:mi:ss')");
			    firstCondition=false;
			}
			else
				sql.append(" and "+fieldName+">=to_date('"+value+"','yyyy-mm-dd hh24:mi:ss')");
		}
		
		return firstCondition;
	}
	
	protected boolean appendNotNullDateBeforeCondition(StringBuffer sql,String fieldName,Object value,boolean firstCondition){
		if(sql==null)
			return true;
		
		if(value!=null&&!"".equals(value.toString().trim())){
			if(firstCondition){
			    sql.append(" where "+fieldName+"<to_date('"+value+"','yyyy-mm-dd hh24:mi:ss')");
			    firstCondition=false;
			}
			else
				sql.append(" and "+fieldName+"<to_date('"+value+"','yyyy-mm-dd hh24:mi:ss')");
		}
		
		return firstCondition;
	}
	
	protected void doClose(ResultSet rs,PreparedStatement psta,Connection connection){
		if(rs!=null)
			try {
				rs.close();
			} 
		    catch (SQLException e) {
			}
		
		if(psta!=null)
			try {
				psta.close();
			} 
		    catch (SQLException e) {
			}
		
		if(connection!=null)
			try {
				connection.close();
			} 
		    catch (SQLException e) {
				e.printStackTrace();
			}
	}
}
